/**
 * 
 */
package com.srini.servlets;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.srini.pojos.LoggedInUser;
import com.srini.pojos.MedicalBillInfo;
import com.srini.util.AppUtil;
import com.srini.util.ConnectionUtil;
import com.vssr.persist.DataBaseWriter;
import com.vssr.persist.DomainObject;
import com.vssr.persist.exception.DomainException;

/**
 * @author Sriniz
 * 
 */
public class MedicalExemptServlet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doPost(req, resp);
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		System.out.println(".... M E D I C A L ....");
		LoggedInUser user = (LoggedInUser) req.getSession().getAttribute("loggedInUser");
		
		String billNo = req.getParameter("billNo");
		String billDate = req.getParameter("billDate");
		String drName = req.getParameter("drName");
		String amount = req.getParameter("amount");
		String patName = req.getParameter("patName");
		String relation = req.getParameter("relation");
		String action = req.getParameter("action");
		String query = null;
		if (AppUtil.isEmpty(action)) {
			String year = null;
			query = "select * from medicalexempt where billNo='" + billNo + "'";
			List list = DomainObject.getDBRecords(query,
					new ConnectionUtil().getConnection());
			if (list != null && list.isEmpty()) {
				MedicalBillInfo bill = new MedicalBillInfo();
				bill.setBillNo(billNo);
				bill.setBillDate(billDate);
				bill.setDoctorName(drName);
				bill.setAmount(Double.parseDouble(amount));
				bill.setPatientName(patName);
				bill.setRelation(relation);
				year = billDate.substring(billDate.length() - 4);
				bill.setYear(year);
				DataBaseWriter.save(bill);

			} else {
				System.out.println("Duplicate Bill Entry");
			}
		} else {
			System.out.println("... E X P O R T ...");
			resp.setContentType("application/vnd.ms-excel");
			InputStream is = this.getClass().getResourceAsStream(
					"/Medical_Exemption_Format.xls");
			HSSFWorkbook wb = new HSSFWorkbook(is);
			exportMedicalBills(wb, user);
			OutputStream out = resp.getOutputStream();
			
			resp.setHeader("Content-Disposition", "attachment; filename="
					+ "MedicalExemptionBills.xls");
			wb.write(out);
			out.close();
		}
	}

	private void exportMedicalBills(HSSFWorkbook wb, LoggedInUser user) {
		String query;
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFFont font = wb.createFont();
		font.setFontName("calibri");
		font.setFontHeightInPoints((short) 9);

		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderBottom((short) 1);
		style.setBorderRight((short) 1);
		style.setFont(font);
		int i = 15, k = 0;
		HSSFRow row = null;
		HSSFCell cell = null;
		
		row = sheet.getRow(1);
		cell = row.getCell(3);
		cell.setCellValue(user.getUserInfo().getFullDisplayName());
		
		cell = row.getCell(6);
		cell.setCellValue(user.getUserInfo().getEmpId());
		
		row = sheet.getRow(2);
		cell = row.getCell(3);
		cell.setCellValue(user.getUserInfo().getProject());
		
		cell = row.getCell(6);
		cell.setCellValue("Enterprise");
		
		
		Calendar cal = Calendar.getInstance();
		query = "select * from medicalexempt where finyear='"
				+ cal.get(Calendar.YEAR) + "'";
		List list = null;
		try {
			list = DomainObject.getDBRecords(new MedicalBillInfo(), query,
					new ConnectionUtil().getConnection());
		} catch (DomainException e) {
			e.printStackTrace();
		}
		
		if (list != null && list.size() > 0) {
			MedicalBillInfo billInfo = null;
			for (; i < list.size() + 15; i++, k++) {

				sheet.shiftRows(i, sheet.getLastRowNum(), 1);
				row = sheet.createRow(i);
				row.setHeightInPoints((short) 20);
				billInfo = (MedicalBillInfo) list.get(k);
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(k + 1);
				cell.setCellStyle(style);

				cell = row.createCell(1);
				cell.setCellValue(billInfo.getBillNo());
				cell.setCellStyle(style);

				cell = row.createCell(2);
				cell.setCellValue(billInfo.getBillDate());
				cell.setCellStyle(style);

				cell = row.createCell(3);
				cell.setCellValue(billInfo.getDoctorName());
				cell.setCellStyle(style);

				cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(billInfo.getAmount());
				cell.setCellStyle(style);

				cell = row.createCell(5);
				cell.setCellValue(billInfo.getPatientName());
				cell.setCellStyle(style);

				cell = row.createCell(6);
				cell.setCellValue(billInfo.getRelation());
				cell.setCellStyle(style);
			}
			row = sheet.getRow(i);
			cell = row.getCell(4);
			cell.setCellFormula("SUM(E15:E" + i + ")");
			row = sheet.getRow(11);
			cell = row.getCell(1);
			cell.setCellFormula("SUM(E15:E" + i + ")");
		}
	}
}
